package com.study.jsqlparser.dto;

import lombok.Data;
import lombok.experimental.Accessors;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.springframework.util.StringUtils;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Data
@Accessors(chain = true)
public class SlowQueryMetadata {
    private int count = 1;
    private String time;
    private String userAndHost;
    private String schema;
    private String queryTime;
    private String bytesSent;
    private String timestamp;
    private String sql;
    private String fileName;
    private String appName;

    /**
     * 和元数据保持一致格式
     */
    @Override
    public String toString() {
        return
                "# " + fileName + "\t" + appName + "\t" + count + "\n" +
                        time + "\n" +
                        userAndHost + "\n" +
                        schema + "\n" +
                        queryTime + "\n" +
                        bytesSent + "\n" +
                        timestamp + "\n" +
                        sql + "\n";
    }

    /**
     * 去重 不同参数的相同格式的sql
     * 计数
     */
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        SlowQueryMetadata slowQueryMetadata = (SlowQueryMetadata) o;
        boolean equals = Objects.equals(uniqueString(sql), uniqueString(slowQueryMetadata.sql));
        if (equals) {
            slowQueryMetadata.setCount(slowQueryMetadata.getCount() + 1);
        }
        return equals;
    }

    @Override
    public int hashCode() {
        return Objects.hash(uniqueString(sql));
    }

    /**
     * 去除sql中的参数
     */
    private String uniqueString(String sql) {
        if (!StringUtils.hasText(sql)) {
            return "";
        }
        try {
            sql = sql.replace("\t", " ");
            sql = sql.replaceAll("\\s+", " ");
            // 替换掉注释部分 /*...*/
            sql = sql.replaceAll("/\\*.*?\\*/", "");
            // 相对比较耗时
            Statement statement = CCJSqlParserUtil.parse(new StringReader(sql));
            if (statement instanceof Insert) {
                return insertStatement((Insert) statement);
            }
            if (statement instanceof Update) {
                return updateStatement((Update) statement);
            }
            if (statement instanceof Delete) {
                return deleteStatement((Delete) statement);
            }
            if (statement instanceof Select) {
                return selectStatement(statement);
            }
        } catch (Exception e) {
            return sql;
        }
        return sql;
    }

    private String selectStatement(Statement statement) {
        List<String> tables = new ArrayList<>(new TablesNamesFinder().getTables(statement));
        Select select = (Select) statement;
        try {
            List<SelectItem<?>> selectItems = select.getPlainSelect().getSelectItems();
            return "select " + selectItems + " " + tables;
        } catch (Exception e) {
            return "select " + tables;
        }
    }

    /**
     * delete语句只要表名相同 删除条件列相同 即可认为是同一条sql
     */
    private String deleteStatement(Delete delete) {
        StringBuilder builder = new StringBuilder("delete ");
        builder.append(delete.getTable().getName().trim());
        builder.append(" where ");
        String[] ands = delete.getWhere().toString().toLowerCase().split("and");
        for (String and : ands) {
            builder.append(and.split("=")[0].trim()).append(" ");
        }
        return builder.toString();
    }

    /**
     * 更新语句只要表名 要更新的列名 where条件列名 相同就认为是同一条sql
     */
    private String updateStatement(Update update) {
        StringBuilder builder = new StringBuilder("update ");
        builder.append(update.getTable().getName().trim());
        builder.append(" column ");
        for (UpdateSet updateSet : update.getUpdateSets()) {
            builder.append(updateSet.getColumns().toString().trim());
        }
        builder.append(" where ");
        String[] ands = update.getWhere().toString().toLowerCase().split("and");
        for (String and : ands) {
            builder.append(and.split("=")[0].trim()).append(" ");
        }
        return builder.toString();
    }

    /**
     * 新增语句只要表相同列相同即可认为是相同sql
     */
    private String insertStatement(Insert statement) {
        return "insert " + statement.getTable().getName() + " " + statement.getColumns().toString();
    }

}
